﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;

namespace Projeto_Trucadores.Classes
{
    public class MyManager
    {
        #region Variaveis Globais

        private string connectionString;

        #endregion


        /// <summary>
        /// Metodo Construtor para a Classe de Banco
        /// </summary>
        /// <param name="strConnection">string de conexão com o banco MySql</param>
        public MyManager()
        {
            connectionString = ConfigurationManager.ConnectionStrings["ConexaoBD"].ConnectionString;
        }

        private MySqlConnection GetConnection()
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            connection.Open();
            return connection;
        }

        #region Commands Sql
        public Object ExecuteScalar(string commandText)
        {
            using (MySqlConnection connection = GetConnection())
            {
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = commandText;
                return cmd.ExecuteScalar();
            }
        }

        public int ExecuteNonQuery(string commandText)
        {
            using (MySqlConnection connection = GetConnection())
            {
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = commandText;
                return cmd.ExecuteNonQuery();
            }
        }

        public DataTable ExecuteCommand(string commandText)
        {
            using (MySqlConnection connection = GetConnection())
            {
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandTimeout = 12000;
                cmd.CommandText = commandText;
                return ExecuteCommand(cmd);
            }
        }

        private DataTable ExecuteCommand(MySqlCommand cmd)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
            adapter.FillLoadOption = LoadOption.OverwriteChanges;
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            DataTable table = new DataTable();
            adapter.Fill(table);
            return table;
        }

        #endregion

        #region Procedures Commands
        public DataTable ExecuteProcedure(string procedureName, List<MySqlParameter> parameters)
        {
            using (MySqlConnection connection = GetConnection())
            {
                MySqlCommand cmd = CreateProcedureCommand(procedureName, parameters, connection);
                return ExecuteCommand(cmd);
            }
        }

        public object ExecuteProcedureScalar(string procedureName, List<MySqlParameter> parameters)
        {
            using (MySqlConnection connection = GetConnection())
            {
                MySqlCommand cmd = CreateProcedureCommand(procedureName, parameters, connection);
                return cmd.ExecuteScalar();
            }
        }

        public int ExecuteProcedureNonQuery(string procedureName, List<MySqlParameter> parameters)
        {
            using (MySqlConnection connection = GetConnection())
            {
                MySqlCommand cmd = CreateProcedureCommand(procedureName, parameters, connection);
                return cmd.ExecuteNonQuery();
            }
        }

        private MySqlCommand CreateProcedureCommand(string procedureName, List<MySqlParameter> parameters, MySqlConnection connection)
        {
            MySqlCommand cmd = connection.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procedureName;
            if ((parameters != null) && (parameters.Count > 0))
            {
                cmd.Parameters.AddRange(parameters.ToArray());
            }
            return cmd;
        }
        #endregion

    }
}
